// 收藏相关数据库查询
import { getDB } from './index.js'

/**
 * 添加收藏
 * @param {string} userId 用户ID
 * @param {number} imageId 图片ID
 * @returns {boolean} 是否成功添加（如果已存在则返回false）
 */
export function addFavorite(userId, imageId) {
  const db = getDB()
  try {
    const stmt = db.prepare(`
      INSERT INTO Favorite (userId, imageId)
      VALUES (?, ?)
    `)
    stmt.run(userId, imageId)
    return true
  } catch (error) {
    // 如果是唯一约束冲突，说明已收藏
    if (error.code === 'SQLITE_CONSTRAINT_UNIQUE') {
      return false
    }
    throw error
  }
}

/**
 * 取消收藏
 * @param {string} userId 用户ID
 * @param {number} imageId 图片ID
 * @returns {boolean} 是否成功删除
 */
export function removeFavorite(userId, imageId) {
  const db = getDB()
  const stmt = db.prepare(`
    DELETE FROM Favorite 
    WHERE userId = ? AND imageId = ?
  `)
  const result = stmt.run(userId, imageId)
  return result.changes > 0
}

/**
 * 检查是否已收藏
 * @param {string} userId 用户ID
 * @param {number} imageId 图片ID
 * @returns {boolean}
 */
export function isFavorite(userId, imageId) {
  const db = getDB()
  const stmt = db.prepare(`
    SELECT COUNT(*) as count
    FROM Favorite
    WHERE userId = ? AND imageId = ?
  `)
  const result = stmt.get(userId, imageId)
  return result.count > 0
}

/**
 * 获取用户的收藏列表
 * @param {string} userId 用户ID
 * @param {object} options 查询选项
 * @param {number} options.page 页码
 * @param {number} options.pageSize 每页数量
 * @param {number} options.offset 偏移量
 * @param {number} options.limit 限制数量
 * @returns {object} { list, total }
 */
export function getUserFavorites(userId, options = {}) {
  const db = getDB()
  const {
    page = 1,
    pageSize = 20,
    offset,
    limit
  } = options

  const skip = offset !== undefined ? offset : (page - 1) * pageSize
  const pageSizeValue = limit !== undefined ? limit : pageSize

  // 查询总数
  const countStmt = db.prepare(`
    SELECT COUNT(*) as total
    FROM Favorite
    WHERE userId = ?
  `)
  const totalResult = countStmt.get(userId)
  const total = totalResult.total

  // 查询列表
  const listStmt = db.prepare(`
    SELECT 
      f.id,
      f.userId,
      f.imageId,
      f.createdAt,
      i.title,
      i.url,
      i.thumbnail,
      i.width,
      i.height,
      i.views,
      i.likes,
      i.createdAt as imageCreatedAt
    FROM Favorite f
    INNER JOIN Image i ON f.imageId = i.id
    WHERE f.userId = ? AND i.status = 1
    ORDER BY f.createdAt DESC
    LIMIT ? OFFSET ?
  `)

  const list = listStmt.all(userId, pageSizeValue, skip).map(row => ({
    id: row.imageId,
    favoriteId: row.id,
    title: row.title,
    url: row.url,
    thumbnail: row.thumbnail,
    width: row.width,
    height: row.height,
    views: row.views,
    likes: row.likes,
    createTime: row.imageCreatedAt,
    favoriteTime: row.createdAt
  }))

  return {
    list,
    total
  }
}

/**
 * 获取收藏统计（管理员用）
 * @param {object} options 查询选项
 * @param {string} options.userId 用户ID（可选）
 * @param {number} options.imageId 图片ID（可选）
 * @param {number} options.page 页码
 * @param {number} options.pageSize 每页数量
 * @returns {object} { list, total }
 */
export function getFavoriteStats(options = {}) {
  const db = getDB()
  const {
    userId,
    imageId,
    page = 1,
    pageSize = 20
  } = options

  let whereClause = 'WHERE 1=1'
  const params = []

  if (userId) {
    whereClause += ' AND f.userId = ?'
    params.push(userId)
  }

  if (imageId) {
    whereClause += ' AND f.imageId = ?'
    params.push(imageId)
  }

  // 查询总数
  const countStmt = db.prepare(`
    SELECT COUNT(*) as total
    FROM Favorite f
    ${whereClause}
  `)
  const totalResult = countStmt.get(...params)
  const total = totalResult.total

  // 查询列表
  const offset = (page - 1) * pageSize
  const listStmt = db.prepare(`
    SELECT 
      f.id,
      f.userId,
      f.imageId,
      f.createdAt,
      i.title as imageTitle,
      i.url as imageUrl,
      i.thumbnail as imageThumbnail
    FROM Favorite f
    LEFT JOIN Image i ON f.imageId = i.id
    ${whereClause}
    ORDER BY f.createdAt DESC
    LIMIT ? OFFSET ?
  `)

  const list = listStmt.all(...params, pageSize, offset)

  return {
    list,
    total,
    page,
    pageSize
  }
}

/**
 * 获取图片的收藏数
 * @param {number} imageId 图片ID
 * @returns {number}
 */
export function getImageFavoriteCount(imageId) {
  const db = getDB()
  const stmt = db.prepare(`
    SELECT COUNT(*) as count
    FROM Favorite
    WHERE imageId = ?
  `)
  const result = stmt.get(imageId)
  return result.count
}

/**
 * 批量获取图片的收藏状态
 * @param {string} userId 用户ID
 * @param {Array<number>} imageIds 图片ID数组
 * @returns {Set<number>} 已收藏的图片ID集合
 */
export function getBatchFavoriteStatus(userId, imageIds) {
  if (!imageIds || imageIds.length === 0) {
    return new Set()
  }

  const db = getDB()
  const placeholders = imageIds.map(() => '?').join(',')
  const stmt = db.prepare(`
    SELECT imageId
    FROM Favorite
    WHERE userId = ? AND imageId IN (${placeholders})
  `)
  const rows = stmt.all(userId, ...imageIds)
  return new Set(rows.map(row => row.imageId))
}

